import os
import random
import time

import openpyxl
import pandas as pd
from openpyxl.reader.excel import load_workbook


def red_min_value(dname, excel_path):
    wb = load_workbook(excel_path)
    sheet = wb['Sheet1']
    sheet.freeze_panes = 'B2'

    # 遍历所有行
    for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row):
        # 计算当前行的宽度
        width = 10
        # 设置当前行所有单元格的宽度
        for cell in row:
            sheet.column_dimensions[cell.column_letter].width = width

    # 标记最小值的背景色为红色
    color = 'FF0000'
    pre_date = '日期'
    for row in sheet[2:sheet.max_row]:
        for cell in row:
            if cell.col_idx == 1:
                if str(cell.value) != pre_date:
                    color = hex(random.randint(2 ** 23 + 4000000, 2 ** 24 - 1))[2:].zfill(6)
                    pre_date = str(cell.value)
            cell.fill = openpyxl.styles.PatternFill(start_color=color, end_color=color, fill_type='solid')
    # 保存工作簿
    current_time = time.strftime('%Y%m%d_%H%M%S', time.localtime())
    color_excel_file_path = f'{dname}_{current_time}.xlsx'  # 加载Excel文件
    if os.path.exists(color_excel_file_path):
        os.remove(color_excel_file_path)
    wb.save(color_excel_file_path)
    return color_excel_file_path


def csv2excel(full_path_csv):
    # csv转excel
    try:
        excel_path = str(full_path_csv).replace('.csv', '.xlsx')
        # 读取CSV文件
        df = pd.read_csv(full_path_csv)
        df_unique = df.drop_duplicates()
        # 将DataFrame写入Excel文件
        df_unique.to_csv(full_path_csv)
        df_unique.to_excel(excel_path, index=False)
        return excel_path
    except Exception as e:
        print(e)


if __name__ == '__main__':
    dname = 'abc11'
    csv_path = f'{dname}.csv'
    csv2excel(csv_path)
    xlsx_path = f'{dname}.xlsx'
    red_min_value(dname, xlsx_path)
